using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Drawing.Printing;
using System.Collections.Generic;
using MBLocale;

public partial class Report : System.Web.UI.Page
{
    //DataSet dsReportInput = null;
    //string sReportName = null;
    //string sTempReportName = null;
    //string absReportPath = null;
    List<Stream> m_streams;
    System.Windows.Forms.PrintDialog printDialog1 = new System.Windows.Forms.PrintDialog();
    int m_currentPageIndex = 0;
    bool bExport = false;
    float dReportWidth = 8f;
    double dReportHeight = 11f;
    string exportType = "";

    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["theme"].ToString();
        this.Title = "MaxBlox - Report";
    }

    private void setPageTitle()
    {
        try
        {
            if (Session["pageTitle"] != null)
            {
                Page.Header.Title = Session["pageTitle"].ToString();
            }
            else
            {
                if (System.IO.File.Exists(HttpContext.Current.Server.MapPath(".") + "\\maxbloxtitle.ini"))
                {
                    System.IO.StreamReader sr = new System.IO.StreamReader(HttpContext.Current.Server.MapPath(".") + "\\maxbloxtitle.ini");
                    Session["pageTitle"] = sr.ReadLine();
                    Page.Header.Title = Session["pageTitle"].ToString();
                    sr.Close();
                    sr = null;
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in setPageTitle function. Error: " + e.Message);
        }
    }

    private void getReportDataset()
    {
        HDDatabase hdd = new HDDatabase();
        DataSet dsReportInput = new DataSet();
        string sReport_id = Request.QueryString["PK"];
        string sSql = "Select * from sy_report where af_row_id='" + sReport_id + "'";
        dsReportInput.Tables.Add(hdd.GetDataTable(sSql, "p_sy_report").Copy());
        sSql = "Select * from sy_report_obj where rpt_id='" + sReport_id + "'";
        dsReportInput.Tables.Add(hdd.GetDataTable(sSql, "p_sy_report_obj").Copy());
        sSql = "Select * from sy_report_att where rpt_id='" + sReport_id + "'";
        dsReportInput.Tables.Add(hdd.GetDataTable(sSql, "p_sy_report_att").Copy());
        Session["ReportInput"] = dsReportInput;
    }

    protected void Page_SaveStateComplete(object sender, EventArgs e)
    {
        if (bExport)
        {
            if (ddlFileFormat.SelectedIndex != 0)
            {
                ReportPrintHelper oPrint = new ReportPrintHelper();
                switch (ddlFileFormat.SelectedValue)
                {
                    case "Excel":
                        oPrint.ExportReport(ref ReportViewer1, EXPORT_TYPE.TYPE_XLS, Context, true, HttpContext.Current.Session["ReportWidth"].ToString());
                        break;
                    case "PDF":
                        oPrint.ExportReport(ref ReportViewer1, EXPORT_TYPE.TYPE_PDF, Context, true, HttpContext.Current.Session["ReportWidth"].ToString());
                        break;
                }
                oPrint = null;
                ddlFileFormat.SelectedValue = "";
                bExport = false;
            }
        }
    }

    private void Print()
    {
        try
        {
            if (m_streams == null || m_streams.Count == 0)
                return;
            PrintDocument printDoc = new PrintDocument();
            PrinterSettings ps = new PrinterSettings();

            if (printDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                ps.PrinterName = printDialog1.PrinterSettings.PrinterName;
                ps.Copies = printDialog1.PrinterSettings.Copies;
                ps.DefaultPageSettings.Color = printDialog1.PrinterSettings.DefaultPageSettings.Color;
                ps.DefaultPageSettings.Landscape = printDialog1.PrinterSettings.DefaultPageSettings.Landscape;
                ps.DefaultPageSettings.Margins = printDialog1.PrinterSettings.DefaultPageSettings.Margins;
                ps.DefaultPageSettings.PaperSize = printDialog1.PrinterSettings.DefaultPageSettings.PaperSize;
                ps.DefaultPageSettings.PaperSource = printDialog1.PrinterSettings.DefaultPageSettings.PaperSource;
                ps.DefaultPageSettings.PrinterResolution = printDialog1.PrinterSettings.DefaultPageSettings.PrinterResolution;
                ps.DefaultPageSettings.PrinterSettings = printDialog1.PrinterSettings.DefaultPageSettings.PrinterSettings;
                ps.Duplex = printDialog1.PrinterSettings.Duplex;
                ps.FromPage = printDialog1.PrinterSettings.FromPage;
                //ps.IsDefaultPrinter = printDialog1.PrinterSettings.IsDefaultPrinter;
                //ps.IsPlotter = printDialog1.PrinterSettings.IsPlotter;
                //ps.LandscapeAngle = printDialog1.PrinterSettings.LandscapeAngle;
                //ps.MaximumCopies = printDialog1.PrinterSettings.MaximumCopies;
                ps.MaximumPage = printDialog1.PrinterSettings.MaximumPage;
                ps.MinimumPage = printDialog1.PrinterSettings.MinimumPage;
                //ps.PaperSizes = printDialog1.PrinterSettings.PaperSizes;
                //ps.PaperSources = printDialog1.PrinterSettings.PaperSources;
                //ps.PrinterResolutions = printDialog1.PrinterSettings.PrinterResolutions;
                ps.PrintFileName = printDialog1.PrinterSettings.PrintFileName;
                ps.PrintRange = printDialog1.PrinterSettings.PrintRange;
                ps.PrintToFile = printDialog1.PrinterSettings.PrintToFile;
                //ps.SupportsColor = printDialog1.PrinterSettings.SupportsColor;
                ps.ToPage = printDialog1.PrinterSettings.ToPage;

                printDoc.PrinterSettings = ps;

                //ps.DefaultPageSettings.Landscape = landscape;
                //ps.DefaultPageSettings.PrinterSettings.DefaultPageSettings.Landscape = landscape;
                //printDoc.PrinterSettings.PrinterName = printDialog1.PrinterSettings.PrinterName;
                //printDoc.PrinterSettings.DefaultPageSettings.Landscape = printDialog1.PrinterSettings.DefaultPageSettings.Landscape;
                //printDoc.PrinterSettings.DefaultPageSettings.PrinterSettings.DefaultPageSettings.Landscape = printDialog1.PrinterSettings.DefaultPageSettings.Landscape;
                //printDoc.PrinterSettings = printDialog1.PrinterSettings.DefaultPageSettings.PrinterSettings;

                if (!printDoc.PrinterSettings.IsValid)
                {
                    string msg = String.Format("Can't find printer \"{0}\".", printDialog1.PrinterSettings.PrinterName);
                    //MessageBox.Show(msg, "Print Error");
                    return;
                }
                printDoc.PrintPage += new PrintPageEventHandler(PrintPage);
                printDoc.Print();
                //printDialog1.Document = new PrintDocument();
                //printDialog1.Document.PrintPage += new PrintPageEventHandler(PrintPage);
                //printDialog1.Document.Print();
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("Print : Error : " + ex.Message);
        }
    }

    private Stream CreateStream(string name, string fileNameExtension, Encoding encoding, string mimeType, bool willSeek)
    {
        Stream stream = new FileStream(name + "." + fileNameExtension,
            FileMode.Create);
        m_streams.Add(stream);
        return stream;
    }


    /// <summary>
    /// Export the given report as an EMF (Enhanced Metafile) file.
    /// </summary>
    private void Export(Microsoft.Reporting.WebForms.LocalReport report, string PageWidth, float PageHeight, float MarginTop, float MarginLeft, float MarginRight, float MarginBottom)
    {
        StringBuilder deviceInfosb = new StringBuilder();
        deviceInfosb.Append("<DeviceInfo>");
        deviceInfosb.Append("<OutputFormat>EMF</OutputFormat>");
        deviceInfosb.Append(string.Format("<PageWidth>{0}in</PageWidth>", PageWidth));
        deviceInfosb.Append(string.Format("<PageHeight>{0}in</PageHeight>", PageHeight));
        deviceInfosb.Append(string.Format("<MarginTop>{0}in</MarginTop>", MarginTop));
        deviceInfosb.Append(string.Format("<MarginLeft>{0}in</MarginLeft>", MarginLeft));
        deviceInfosb.Append(string.Format("<MarginRight>{0}in</MarginRight>", MarginRight));
        deviceInfosb.Append(string.Format("<MarginBottom>{0}in</MarginBottom>", MarginBottom));
        deviceInfosb.Append(string.Format("</DeviceInfo>"));
        string deviceInfo = deviceInfosb.ToString();
        Microsoft.Reporting.WebForms.Warning[] warnings;
        m_streams = new List<Stream>();
                //Warning[] warnings;
        //string[] streamids;
        //string mimeType;
        //string encoding;
        //string filenameExtension;
        //report.Render("Image", null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
        report.Render("Image", deviceInfo, CreateStream, out warnings);
        foreach (Stream stream in m_streams)
        {
            stream.Position = 0;
        }
    }

    /// <summary>
    /// Handler for PrintPageEvents
    /// </summary>
    private void PrintPage(object sender, PrintPageEventArgs ev)
    {
        System.Drawing.Imaging.Metafile pageImage = new System.Drawing.Imaging.Metafile(m_streams[m_currentPageIndex]);
        ev.Graphics.DrawImage(pageImage, ev.PageBounds);
        m_currentPageIndex++;
        ev.HasMorePages = (m_currentPageIndex < m_streams.Count);
    } 

    protected void Page_Load(object sender, EventArgs e)
    {
        security hdds = new security();
        if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL, true);
        if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL, true);

        try
        {
            setPageTitle();
            setBackButtonLink();
            if (!IsPostBack)
            {
                MyRDLC myrdlc = new MyRDLC();
                initializeValues(ref myrdlc);
                System.IO.MemoryStream msReport = null;

                if (Request.QueryString["PK"] != null)
                    if (Request.QueryString["PK"].Trim() != "")
                        getReportDataset();

                if (Session["ReportInput"] != null)
                {
                    DataSet dsReportInput = (DataSet)Session["ReportInput"];
                    string sSql = getSql(dsReportInput);
                    if (sSql == "") return;
                    //Session["ReportInput"] = null;
                    //myrdlc.GenerateReportFromDataset(dsReportInput, ref msReport, ref dReportWidth);
                    //string sSql = getSql(dsReportInput);
                    SqlDataSource1.SelectCommand = sSql;
                    //if (msReport != null)
                    //{
                        string sReportName = getReportProperties(dsReportInput, "rpt_name");
                        if (sReportName == "") sReportName = getReportProperties(dsReportInput, "rpt_id");
                        ReportViewer1.LocalReport.DisplayName = sReportName;

                        if (dsReportInput.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
                        {
                            //DataTable dt = getDataTable(sSql);
                            getDataFromSQLandFormat objData = new getDataFromSQLandFormat(sSql);
                            DataTable dt = objData.getData(ref dsReportInput);
                            dsReportInput.Tables.Add(dt.Copy()); // Added by srivatsan on Nov 10 2009 for data alignment
                            objData = null;
                            if (dt != null)
                            {
                                myrdlc.GenerateReportFromDataset(dsReportInput, ref msReport, ref dReportWidth, exportType);
                                ReportViewer1.LocalReport.DataSources.Clear();
                                ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", dt));
                            }
                        }
                        else
                        {
                            getDataFromSQLandFormat objData = new getDataFromSQLandFormat(sSql);
                            DataTable dt1 = objData.getData(ref dsReportInput);
                            objData = null;
                            dsReportInput.Tables.Add(dt1.Copy());
                            myrdlc.GenerateReportFromDataset(dsReportInput, ref msReport, ref dReportWidth, exportType);
                            ReportViewer1.LocalReport.DataSources.Clear();
                            ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", dt1));
                            dt1.Dispose();
                            dt1 = null;
                        }
                        ReportViewer1.LocalReport.LoadReportDefinition(msReport);
                        ReportViewer1.LocalReport.Refresh();
                        msReport.Dispose();

                        //Microsoft.Reporting.WebForms.LocalReport report = ReportViewer1.LocalReport;
                        //Export(report, 11.69f, 8.27f, 0f, 0f, 0f, 0f);
                        //m_currentPageIndex = 0;

                        //Print(true); foreach (Stream s in m_streams)
                        //{
                        //    s.Close();
                        //}
                    //}
                }
            }
            else if (IsPostBack)
            {
                if (Request.QueryString["PK"] != null)
                    if (Request.QueryString["PK"].Trim() != "")
                        getReportDataset();

                DataSet dsInputInfo = (DataSet)Session["ReportInput"];
                if (dsInputInfo.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
                {

                    string sSqlId = dsInputInfo.Tables["p_sy_report_obj"].Rows[0]["object_id"].ToString();

                    HDDatabase hdd = new HDDatabase();
                    string sSQL = hdd.GetColumnValue("Select report_base_sql from sy_report_sql where report_base_id=" + sSqlId);
                    Hashtable hsParam = new Hashtable();
                    hsParam = getParameters(sSQL, "$", ref hsParam);
                    hsParam = getParameters(sSQL, "#", ref hsParam);
                    DrawParameterTable(ref hsParam);
                    SetParameter(ref hsParam);
                    hsParam = null;
                }
            }

            bool isExport = getExportValue();
            if (isExport)
            {
                lbFF.Visible = true;
                ddlFileFormat.Visible = true;
                lnkExport.Visible = true;
                //lnkPrint.Visible = true;
            }
            else
            {
                lbFF.Visible = false;
                ddlFileFormat.Visible = false;
                lnkExport.Visible = false;
                //lnkPrint.Visible = false;
            }

            #region MyRegion
            //if (Session["ReportPath"] != null)
            //{
            //    absReportPath = Session["ReportPath"].ToString();
            //    Session.Remove("ReportPath");
            //    absReportPath = @"D:\MaxBlox\app_frame\report\New_admin_200813144846721.rdlc";
            //    if (System.IO.File.Exists(absReportPath))
            //    {
            //        //dsReportInput = (DataSet)Session["ReportInput"];
            //        //sReportName = getReportProperties("rpt_name");
            //        //if (sReportName == "") sReportName = getReportProperties("rpt_id");
            //        //this.Title = "MaxBlox - Report - " + sReportName;
            //        //string sReportType = getReportProperties("rpt_type");
            //        XmlDocument xDoc = new XmlDocument();
            //        xDoc.Load(absReportPath);
            //        XmlNodeList xmlNodes = xDoc.GetElementsByTagName("CommandText");
            //        if (xmlNodes.Count > 0)
            //        {
            //             sSql = xmlNodes[0].InnerText;
            //        }
            //        xDoc = null;
            //        SqlDataSource1.ConnectionString = constr;
            //        SqlDataSource1.SelectCommand = sSql;

            //        //MyRDLC mrdlc = new MyRDLC();
            //        //mrdlc.serverPath = Server.MapPath("mystyle.txt");
            //        //int ind = mrdlc.serverPath.LastIndexOf('\\');
            //        //mrdlc.serverPath = mrdlc.serverPath.Substring(0, ind);
            //        //mrdlc.connectionString = constr;
            //        //mrdlc.OpenConnection();

            //        //bool bHasRows = mrdlc.GenerateFieldsList(sSql);
            //        //mrdlc.init();
            //        //string sTempReportName = sReportName + "_" + HttpContext.Current.Session["userid"].ToString() + "_" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
            //        //if (!System.IO.Directory.Exists(mrdlc.serverPath + "\\report"))
            //        //    System.IO.Directory.CreateDirectory(mrdlc.serverPath + "\\report");
            //        //absReportPath = mrdlc.serverPath + "\\report\\" + sTempReportName + ".rdlc";
            //        //mrdlc.commandText = sSql;
            //        //mrdlc.writeInitRDLC(absReportPath);
            //        //mrdlc.writeDataSource("DataSource1");
            //        //mrdlc.writeReportParameter();
            //        //mrdlc.writeDataset();

            //        //if (sReportType == "") sReportType = "tabular";
            //        //switch (sReportType.ToLower())
            //        //{
            //        //    default:
            //        //    case "tabular":
            //        //        mrdlc.GenerateRdl(absReportPath, sReportName, getReportProperties("rpt_desc"));
            //        //        break;
            //        //    //    /*case "matrix":
            //        //    //        mrdlc.GenerateMatrixRdl(absReportPath);
            //        //    //        break;*/
            //        //}
            //        ////mrdlc.writeNoRecordsFooter();
            //        ////*if (sr.reportType.Equals("Matrix"))
            //        ////{
            //        ////    mrdlc.GenerateMatrixRdl(absReportPath);
            //        ////}
            //        ////else if (sr.reportType.Equals("Master"))
            //        ////{
            //        ////    mrdlc.GenerateMasterDetailRdl(absReportPath);
            //        ////}
            //        ////else
            //        ////{
            //        ////    mrdlc.GenerateRdl(absReportPath);
            //        ////}*/
            //        //mrdlc.writeEndRDLC();
            //        //this.Title = sReportName;
            //        //ReportViewer1.LocalReport.DisplayName = sReportName;
            //        ////ReportViewer1.LocalReport.LoadReportDefinition(ReportStream);

            //        //ReportViewer1.LocalReport.EnableExternalImages = true;
            //        //ReportViewer1.LocalReport.ReportPath =  absReportPath;//@"D:\MaxBlox\app_frame\report\Test Report Name - 1002_admin_2007122715414283.rdlc";@"D:\MaxBlox\app_frame\report\Business Unit_admin_20071228133112970.rdlc";//
            //        //ReportViewer1.LocalReport.Refresh();
            //    }
            //}

            #endregion

            
            //imgliteral.Text = "<img class=\"linkBacksmalllogo\" onclick=\"javascript:window.history.back(-1); return false;\"></img>";
            //imgback.CssClass = "linkBacksmalllogo";
            //imgback.OnClientClick = "javascript:window.history.back(-1); return false;";
            //imgback.ToolTip = "Back";
            //imghome.CssClass = "linkhomesmalllogo";
            //imghome.OnClientClick = "javascript:window.location.href='" + homelink() + "';return false;";
            //imghome.ToolTip = "Home";
        }

        catch (Exception ex)
        {
            lbFF.Visible = false;
            ddlFileFormat.Visible = false;
            lnkExport.Visible = false;
            //lnkPrint.Enabled = false;
            LogWriter.WriteLog("Error : " + ex.Message);
            Response.Redirect("Error.aspx");
        }
        isViewMode();
    }
    private void isViewMode()
    {
        if (HttpContext.Current.Request.QueryString["VMODE"] != null)
            if (HttpContext.Current.Request.QueryString["VMODE"] == "1")
            {
                DynamicDisplayI ddi = new DynamicDisplayI();
                ddi.disableControls(this);
                ddi = null;
            }
    }    
    private void ProcessDataTable(ref DataTable dtData)
    {
        DynamicDisplayI DDI = new DynamicDisplayI();
        try
        {
            System.Globalization.CultureInfo ciUserLocale = MBLocalization.getCultureInfo(HttpContext.Current.Session["userlocale"].ToString());
            string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");

            foreach (DataColumn dc in dtData.Columns)
                if (dc.DataType == Type.GetType("System.DateTime"))
                {
                    foreach (DataRow dr in dtData.Rows)
                    {
                        if (dr[dc.ColumnName] != System.DBNull.Value)
                        {
                            DateTime dt = DateTime.Parse(dr[dc.ColumnName].ToString(), ciUserLocale);
                            dr[dc.ColumnName] = dt.ToString();
                        }
                        //dr[dc.ColumnName] = DDI.getUsersDT(dr[dc.ColumnName].ToString(), sUTC_Offset);
                    }
                }
            ciUserLocale = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("ProcessDataTable : Error : " + ex.Message);
        }
        finally
        {
            DDI = null;
        }
    }

    private DataTable getDataTable(string Query)
    {
        DataTable dtData = new DataTable();
        SqlDataAdapter objAdapter = null;
        SqlConnection sqlConn = null;
        SqlCommand command = null;
        try
        {
            sqlConn = new SqlConnection(HttpContext.Current.Session["dbcon"].ToString());
            sqlConn.Open();
            command = sqlConn.CreateCommand();
            command.CommandText = Query;
            objAdapter = new SqlDataAdapter(command);
            objAdapter.Fill(dtData);
            sqlConn.Close();

            //if (dtData.Rows.Count > 0 && dtData.Columns.Contains("created_by"))
            // Added by srivatsan on June 15 2009 for owner id field
            if (dtData.Rows.Count > 0 && dtData.Columns.Contains("owner_id"))
            {
                SecurityFilter SF = new SecurityFilter();
                DataSet dseObj = SF.GetFilterDataset(Query);
                dtData = dseObj.Tables[0].Copy();
                dseObj.Dispose();
                dseObj = null;
            }
            ProcessDataTable(ref dtData);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getDataSet : Error : " + ex.Message);
            dtData = null;
        }

        finally
        {
            objAdapter.Dispose();
            sqlConn.Dispose();
            command.Dispose();
            objAdapter = null;
            sqlConn = null;
            command = null;
        }
        return dtData;
    }

    private void initializeValues(ref MyRDLC myrdlc)
    {
        try
        {
            string sBG0 = myrdlc.getCSSPropertyValue(".Grid_Row0", "background-color");
            string sBG1 = myrdlc.getCSSPropertyValue(".Grid_Row1", "background-color");
            if (sBG0 == "") sBG0 = "#FFFFFF";
            string constr = Session["dbcon"].ToString();

            SqlDataSource1.ConnectionString = constr;

            ReportViewer1.BackColor = System.Drawing.ColorTranslator.FromHtml(sBG1);//System.Drawing.ColorTranslator.FromHtml(myrdlc.getCSSPropertyValue(".Dynamic_PageHeaderRow", "background-color"));
            ReportViewer1.BorderStyle = BorderStyle.None;
            //ReportViewer1.BorderWidth = System.Web.UI.WebControls.Unit.Point(2);
            //ReportViewer1.BorderColor = System.Drawing.ColorTranslator.FromHtml(sBG0);
            ReportViewer1.Reset();
            ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", "SqlDataSource1"));
            ReportViewer1.LocalReport.EnableExternalImages = true;
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("initializeValues Error : " + ex.Message);
            Response.Redirect("Error.aspx");
        }
    }

    private string getCustodianFilter(string sTableName)
    {
        string sCustodian = "";
        try
        {
            HDDatabase hdd = new HDDatabase();
            string sSql = "select custodian_filter from sy_usr where af_row_id='" + Session["user_afrowid"].ToString() + "'";
            string sTemp = hdd.GetColumnValue(sSql);
            if (Session["userid"].ToString() == "afadmin")
            {
                sCustodian = "";
            }
            else if (sTemp.ToLower() == "ar" || Session["user_afrowid"].ToString() == "650a333596bbf348" || sTemp.Trim() == "")
            {
                ////sCustodian = Tablename + ".created_by != 'e51e0327224a2eb9'";
                //sCustodian = "[" + sTableName + "].created_by <> 'e51e0327224a2eb9'";
                // Added by srivatsan on June 15 2009 for owner id field
                sCustodian = "[" + sTableName + "].owner_id <> 'e51e0327224a2eb9'";
            }
            else if (sTemp.ToLower() == "mr")
            {
                //sCustodian = "[" + sTableName + "].created_by='" + Session["user_afrowid"].ToString() + "'";//sCustodian = "created_by='" + Session["user_afrowid"].ToString() + "'";
                // Added by srivatsan on June 15 2009 for owner id field
                sCustodian = "[" + sTableName + "].owner_id='" + Session["user_afrowid"].ToString() + "'";//sCustodian = "created_by='" + Session["user_afrowid"].ToString() + "'";
            }
            hdd = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getCustodianFilter : Error : " + ex.Message);
        }
        return sCustodian;
    }

    private string getSql(DataSet dsInputInfo)
    {
        string sSql = "";
        if (dsInputInfo.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
        {
            string sSqlId = dsInputInfo.Tables["p_sy_report_obj"].Rows[0]["object_id"].ToString();
            sSql = getPredefinedSQL(sSqlId);
        }
        else
        {
            getDataFromSQLandFormat objData = new getDataFromSQLandFormat();
            sSql = objData.getSql(ref dsInputInfo);
            //objData = new getDataFromSQLandFormat(sSql);
            //DataTable dt1 = objData.getData(ref dsInputInfo);
            //objData = null;
            //dsInputInfo.Tables.Add(dt1.Copy());
            //dt1.Dispose();
            //dt1 = null;
            objData = null;
            //security hdds = new security();
            //string sTableName = getObjectName(dsInputInfo.Tables["p_sy_report_obj"].Rows[0]["object_id"].ToString());
            //string sFieldName = getFieldNames(dsInputInfo.Tables["p_sy_report_att"]);
            //string sCustodian = getCustodianFilter(sTableName);
            //string sWhereCondition = getWhereCondition(dsInputInfo.Tables["p_sy_report_obj"], dsInputInfo.Tables["p_sy_report_att"], ref sTableName);
            //string sRowLevelSecurity = hdds.getRowLevelSecurityData(sTableName, sTableName);

            //if (sCustodian != "")
            //    if (sWhereCondition != "")
            //        sWhereCondition = sWhereCondition + " and " + sCustodian;
            //    else
            //        sWhereCondition = sWhereCondition + " " + sCustodian;


            //if (sFieldName != "")
            //{
            //    sSql = "Select " + sFieldName + " from " + sTableName;
            //}

            //if (sWhereCondition != "")
            //{
            //    sSql = sSql + " where " + sWhereCondition;
            //}
            //else
            //    sSql = sSql + " where 1=1";

            //if (sRowLevelSecurity != "")
            //{
            //    sSql = sSql + " and " + sRowLevelSecurity;
            //}
            //hdds = null;
        }

        return sSql;
    }

    //private string getSql()
    //{
    //    string sSql = "";
    //    if (dsInputInfo.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
    //    {
    //        string sSqlId = dsInputInfo.Tables["p_sy_report_obj"].Rows[0]["object_id"].ToString();
    //        sSql = getPredefinedSQL(sSqlId);
    //    }
    //    else
    //    {
    //        string sTableName = getObjectName(dsInputInfo.Tables["p_sy_report_obj"].Rows[0]["object_id"].ToString());
    //        string sFieldName = getFieldNames(dsInputInfo.Tables["p_sy_report_att"], sTableName);
    //        string sCustodian = getCustodianFilter(sTableName);
    //        string sWhereCondition = getWhereCondition(dsInputInfo.Tables["p_sy_report_obj"], dsInputInfo.Tables["p_sy_report_att"], ref sTableName);
    //        string sRowLevelSecurity = hdds.getRowLevelSecurityData(sTableName, sTableName);
    //        if (sCustodian != "")
    //            if (sWhereCondition != "")
    //                sWhereCondition = sWhereCondition + " and " + sCustodian;
    //            else
    //                sWhereCondition = sWhereCondition + " " + sCustodian;

    //        if (sFieldName != "")
    //        {
    //            sSql = "Select " + sFieldName + " from " + sTableName;
    //        }

    //        if (sWhereCondition.Trim() != "")
    //        {
    //            sSql = sSql + " where " + sWhereCondition;
    //        }
    //        else
    //            sSql = sSql + " where 1=1";

    //        if (sRowLevelSecurity != "")
    //        {
    //            sSql = sSql + " and " + sRowLevelSecurity;
    //        }
    //    }

    //    return sSql;
    //}

    private string getObjectName(string ObjectId)
    {
        HDDatabase hdd = new HDDatabase(Session["dbcon"].ToString());
        string sObjectName = hdd.GetColumnValue("Select object_table from sy_obj where object_id = '" + ObjectId + "'");
        return sObjectName;
    }

    private string getPredefinedSQL(string SQL_id)
    {
        HDDatabase hdd = new HDDatabase(Session["dbcon"].ToString());
        string sSQL = hdd.GetColumnValue("Select report_base_sql from sy_report_sql where report_base_id=" + SQL_id);
        Hashtable hsParam = new Hashtable();
        hsParam = getParameters(sSQL, "$", ref hsParam);
        hsParam = getParameters(sSQL, "#", ref hsParam);
        if ((sSQL.Contains("$") || sSQL.Contains("#")) && !IsPostBack)
        {
            DrawParameterTable(ref hsParam);
            btn_Generate.Visible = true;
            lbFF.Visible = false;
            ddlFileFormat.Visible = false;
            lnkExport.Visible = false;
            //lnkPrint.Enabled = false;
            sSQL = "";
            hsParam = null;
        }
        else
            sSQL = ReplaceParameters(sSQL);
        return sSQL;
    }

    private string ReplaceParameters(string SQL)
    {
        string sTemp = "ctl00$ContentPlaceHolder3$wz_Report$txt_";
        sTemp = "txt_";
        if (HttpContext.Current.Request.UrlReferrer != null)
        {
            if (HttpContext.Current.Request.UrlReferrer.AbsolutePath.ToLower().EndsWith("report.aspx"))
                sTemp = "txt_";
            else if (HttpContext.Current.Request.UrlReferrer.AbsolutePath.ToLower().EndsWith("/appframe/reportwizard.aspx"))
                sTemp = "ctl00$ContentPlaceHolder3$wz_Report$txt_";
            Hashtable hsParam = new Hashtable();
            hsParam = getParameters(SQL, "$", ref hsParam);
            hsParam = getParameters(SQL, "#", ref hsParam);
            foreach (string sParam in hsParam.Keys)
            {
                if (sParam.StartsWith("#") && sParam.EndsWith("#"))
                    SQL = SQL.Replace(sParam, GetLocalizedDateTime(sTemp, sParam));
                else
                    SQL = SQL.Replace(sParam, HttpContext.Current.Request.Form[sTemp + sParam]);
            }
            hsParam = null;
        }
        return SQL;
    }

    private string GetLocalizedDateTime(string sTemp, string sParam)
    {
        string sRet = "";
        try
        {
            sRet = Request.Form[sTemp + sParam];
            #region Old code commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
            //Localization li = new Localization();
            //DateTime dt = DateTime.Parse(sRet, li.getUserCulture());
            //sRet = dt.ToString();
            //li = null;
            #endregion
            sRet = DateTime.Parse(sRet, MBLocalization.getCultureInfo(HttpContext.Current.Session["userlocale"].ToString())).ToString();
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("ReplaceDateTime : Error : " + Ex.Message);
            //sRet = Request.Form["ctl00$ContentPlaceHolder3$wz_Report$txt_" + sParam];
        }
        return sRet;
    }

    private Hashtable getParameters(string sSql, string sChar, ref Hashtable hsParam)
    {
        //Hashtable hsParam = new Hashtable();
        try
        {
            //HDDatabase hdd = new HDDatabase();
            //DataRow dr = hdd.GetTableRow("select * from sy_report_sql where report_base_id=" + cmbObject.SelectedItem.Value);
            //if (dr != null)
            //{
                //string sSql = dr["report_base_sql"].ToString();
                int iPos = 0;

                while (iPos < sSql.Length & iPos != -1)
                {
                    string sParam = "";
                    iPos = sSql.IndexOf(sChar, iPos);
                    if (iPos != -1)
                    {
                        int iEndPos = sSql.IndexOf(sChar, iPos + 1);
                        if (iEndPos == -1)
                            iEndPos = sSql.Length - iPos;
                        else
                            iEndPos = iEndPos - (iPos - 1);
                        sParam = sSql.Substring(iPos, iEndPos);
                        sParam = sParam.TrimEnd('\'');
                        if (!hsParam.Contains(sParam))
                            hsParam.Add(sParam, "");
                        iPos = iPos + iEndPos;
                    }
                }

               //hsParam = getParameters(sSql, "#",ref hsParam);
            //}
            //hdd = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getParameters : Error : " + Ex.Message);
        }

        return hsParam;
    }

    private string getUTCHours(string sUTC_Offset)
    {
        string sHours = "+0";
        try
        {
            if (sUTC_Offset != "")
            {
                if (sUTC_Offset.IndexOf(".") != -1)
                    sHours = sUTC_Offset.Substring(0, sUTC_Offset.IndexOf('.'));
                else
                    sHours = sUTC_Offset.Trim();

                sHours = sHours.Replace("+", "").Replace("-", "");

                if (sUTC_Offset.StartsWith("-"))
                    sHours = "-" + sHours;
                else
                    sHours = "+" + sHours;
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getUTCHours : Error : " + ex.Message);
            return "+0";
        }
        return sHours;
    }

    private string getUTCMinutes(string sUTC_Offset)
    {
        string sMinutes = "+0";
        try
        {
            if (sUTC_Offset != "")
            {
                sMinutes = sUTC_Offset.Substring(sUTC_Offset.IndexOf('.') + 1, sUTC_Offset.Length - sUTC_Offset.IndexOf('.') - 1);

                sMinutes = sMinutes.Replace("+", "").Replace("-", "");

                if (sUTC_Offset.StartsWith("-"))
                    sMinutes = "-" + sMinutes;
                else
                    sMinutes = "+" + sMinutes;
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getUTCMinutes : Error : " + ex.Message);
            return "+0";
        }
        return sMinutes;
    }

    private string getDTSQL(string ObjectName, string FieldName, string sHours, string sMinutes)
    {
        string sDateAdd = "";
        try
        {
            if (sMinutes != "")
                sDateAdd = "dateadd(mi, " + sMinutes + ", [" + ObjectName + "].[" + FieldName + "])";
            if (sHours != "")
            {
                if (sDateAdd == "")
                    sDateAdd = "dateadd(hh, " + sHours + ", [" + ObjectName + "].[" + FieldName + "])";
                else
                    sDateAdd = "dateadd(hh, " + sHours + "," + sDateAdd + ")";
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getDTSQL : Error : " + ex.Message);
            return FieldName;
        }
        return sDateAdd;
    }

    private string getWhereCondition(DataTable dtObject, DataTable dtFields, ref string sTableName)
    {
        string sWhere = "";
        DataRow[] drObjects = dtFields.Select("object_id <> '" + dtObject.Rows[0]["object_id"].ToString() + "'");
        if (drObjects.Length > 0)
        {
            string sMasterObject = getObjectName(drObjects[0]["object_id"].ToString());
            string sChildObject = getObjectName(dtObject.Rows[0]["object_id"].ToString());
            sWhere = "[" + sChildObject + "].[" + drObjects[0]["attrib_id"].ToString() + "]=[" + sMasterObject + "].[af_row_id]";
            sTableName = sMasterObject + "," + sChildObject;
        }
        return sWhere;
    }

    private string getUserIdSQL(string FieldName, string ObjectName)
    {
        string sUserSQL = "";
        try
        {
            sUserSQL = "(SELECT user_id AS " + FieldName + " from sy_usr as usr where (usr.af_row_id=" + ObjectName + "." + FieldName + ")) as " + FieldName + " ,";
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getUserIdSQL : Error : " + ex.Message);
            sUserSQL = FieldName;
        }

        return sUserSQL;
    }

    private string getFieldNames(DataTable dtAttribute)
    {
        string sFields = "";
        DynamicDisplayI DDI = new DynamicDisplayI();
        string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");

        string sHrs = getUTCHours(sUTC_Offset);
        string sMins = getUTCMinutes(sUTC_Offset);
        string sObject_name = "";
        HDDatabase hdd = new HDDatabase(Session["dbcon"].ToString());
        foreach (DataRow dr in dtAttribute.Rows)//Select("", "attrib_sequence asc")
        {
            string sAttrib_id = dr["attrib_id"].ToString();
            sObject_name = getObjectName(dr["object_id"].ToString());
            //if (sAttrib_id.ToLower() == "created_by" || sAttrib_id.ToLower() == "modified_by")
            // Added by srivatsan on June 15 2009 for owner id field
            if (sAttrib_id.ToLower() == "owner_id" || sAttrib_id.ToLower() == "modified_by")
            {
                sFields += getUserIdSQL(sAttrib_id, sObject_name);
            }
            else
            {
                string sDatatype = hdd.GetColumnValue("select attrib_data_type from sy_obj_att where object_id='" + dr["object_id"].ToString() + "' and attrib_id = '" + sAttrib_id + "'");

                if (sDatatype == "DT")
                {
                    string sDateAdd = getDTSQL(sObject_name, sAttrib_id, sHrs, sMins);
                    sFields = sFields + sDateAdd + " as " + sAttrib_id + ", ";
                }
                else
                    sFields = sFields + " [" + sObject_name + "].[" + sAttrib_id + "], "; //+ "] as [" + dr["caption"].ToString() + "], " ;
            }
        }

        sFields = sFields.Trim();
        //sFields = sFields + " [" + sObject_name + "].[created_by]";
        // Added by srivatsan on June 15 2009 for owner id field
        sFields = sFields + " [" + sObject_name + "].[owner_id]";
        if (sFields.EndsWith(","))
            sFields = sFields.Remove(sFields.Length - 1, 1);

        return sFields;
    }


    private string getReportProperties(DataSet dsReportInput, string FieldName)
    {
        string sReportName = "";
        if (dsReportInput.Tables["p_sy_report"] != null)
        {
            sReportName = dsReportInput.Tables["p_sy_report"].Rows[0][FieldName].ToString();
        }
        return sReportName;
    }

    protected void Page_UnLoad(object sender, EventArgs e)
    {
        //if (absReportPath != null)
        //    if (absReportPath != "")
        //        if (System.IO.File.Exists(absReportPath))
        //            System.IO.File.Delete(absReportPath);

        //if (HttpContext.Current.Session["ReportInput"] != null)
        //    HttpContext.Current.Session.Remove("ReportInput");
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        try
        {
            bExport = true;
            //ReportPrintHelper oPrint = new ReportPrintHelper();
            //switch (ddlFileFormat.SelectedValue)
            //{
            //    case "Excel":
            //        oPrint.ExportReport(ref ReportViewer1, EXPORT_TYPE.TYPE_XLS, Context, false);
            //        break;
            //    case "PDF":
            //        oPrint.ExportReport(ref ReportViewer1, EXPORT_TYPE.TYPE_PDF, Context, false);
            //        break;
            //}
            //oPrint = null;
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("btnExport_Click : Error : " + ex.Message);
        }
    }

    protected void btnPrint_Click(object sender, EventArgs e)
    {
        Microsoft.Reporting.WebForms.LocalReport report = ReportViewer1.LocalReport;
        Export(report, HttpContext.Current.Session["ReportWidth"].ToString() /*dReportWidth*/, 8.27f, 0.3f, 0.3f, 0.3f, 0.3f);//Export(report, 11.69f, 8.27f, 0f, 0f, 0f, 0f);
        m_currentPageIndex = 0;

        Print(); foreach (Stream s in m_streams)
        {
            s.Close();
        }
    }
    protected void ddlFileFormat_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlFileFormat.SelectedIndex != 0)
            lnkExport.Enabled = true;
        else
            lnkExport.Enabled = false;
    }

    private void DrawParameterTable(ref Hashtable hsParam)
    {
        try
        {
            if (hsParam.Count > 0)
            {
                DynamicDisplayI DDI = new DynamicDisplayI();
                HtmlTableCell ht = (HtmlTableCell)DDI.FindControl(this.Controls, "td_param");
                DDI = null;
                if (ht != null)
                {
                    HtmlTable htTable = new HtmlTable();
                    htTable.CellSpacing = 1;
                    htTable.CellPadding = 2;
                    htTable.Attributes.Add("runat", "server");
                    HtmlTableRow htr = new HtmlTableRow();
                    HtmlTableCell htc = new HtmlTableCell();
                    htr.Attributes.Add("class", "Grid_Header");
                    htc = new HtmlTableCell();
                    htc.InnerText = "Parameter   ";
                    htr.Cells.Add(htc);
                    htc = new HtmlTableCell();
                    htc.InnerText = "Value";
                    htr.Cells.Add(htc);
                    htTable.Rows.Add(htr);
                    foreach (string sParam in hsParam.Keys)
                    {
                        htr = new HtmlTableRow();
                        htc = new HtmlTableCell();
                        htr.Attributes.Add("class", "controlFont");
                        htc.InnerText = sParam.Replace("$", "").Replace("#", "") + "   ";
                        htr.Cells.Add(htc);
                        htc = new HtmlTableCell();
                        TextBox txt = new TextBox();
                        txt.ID = "txt_" + sParam;
                        //txt.Width = Unit.Parse("100");
                        htc.Controls.Add(txt);
                        htr.Cells.Add(htc);
                        htTable.Rows.Add(htr);
                    }
                    ht.Controls.Add(htTable);
                }
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("DrawParameterTable : Error : " + Ex.Message);
        }
    }

    private void SetParameter(ref Hashtable hsParam)
    {
        try
        {
            DynamicDisplayI DDI = new DynamicDisplayI();
            foreach (string sParam in hsParam.Keys)
            {
                TextBox txt = (TextBox)DDI.FindControl(this.Controls, "txt_" + sParam);
                if (txt != null)
                {
                    txt.Text = Request.Form["txt_" + sParam];
                }
            }
            DDI = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("SetParameter : Error : " + Ex.Message);
        }
    }

    protected void btn_Generate_Click(object sender, EventArgs e)
    {
        GenerateReport();   
    }

    private void GenerateReport()
    {
        try
        {
            MyRDLC myrdlc = new MyRDLC();
            initializeValues(ref myrdlc);
            System.IO.MemoryStream msReport = null;

            if (Request.QueryString["PK"] != null)
                if (Request.QueryString["PK"].Trim() != "")
                    getReportDataset();

            if (Session["ReportInput"] != null)
            {
                DataSet dsReportInput = (DataSet)Session["ReportInput"];
                string sSql = getSql(dsReportInput);
                if (sSql == "") return;
                SqlDataSource1.SelectCommand = sSql;
                string sReportName = getReportProperties(dsReportInput, "rpt_name");
                if (sReportName == "") sReportName = getReportProperties(dsReportInput, "rpt_id");
                ReportViewer1.LocalReport.DisplayName = sReportName;

                if (dsReportInput.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
                {
                    getDataFromSQLandFormat objData = new getDataFromSQLandFormat(sSql);
                    DataTable dt = objData.getData(ref dsReportInput);
                    objData = null;
                    if (dt != null)
                    {
                        myrdlc.GenerateReportFromDataset(dsReportInput, ref msReport, ref dReportWidth, exportType);
                        ReportViewer1.LocalReport.DataSources.Clear();
                        ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", dt));
                        ReportViewer1.Visible = true;
                    }
                    else
                    {
                        lbFF.Visible = false;
                        ddlFileFormat.Visible = false;
                        lnkExport.Visible = false;
                    }
                }
                ReportViewer1.LocalReport.LoadReportDefinition(msReport);
                ReportViewer1.LocalReport.Refresh();
                msReport.Dispose();

                //myrdlc.GenerateReportFromDataset(dsReportInput, ref msReport, ref dReportWidth);
                //string sSql = getSql(dsReportInput);
                //SqlDataSource1.SelectCommand = sSql;
                //if (msReport != null)
                //{
                //    string sReportName = getReportProperties(dsReportInput, "rpt_name");
                //    if (sReportName == "") sReportName = getReportProperties(dsReportInput, "rpt_id");
                //    ReportViewer1.LocalReport.DisplayName = sReportName;

                //    if (dsReportInput.Tables["p_sy_report"].Rows[0]["rpt_base_object"].ToString().ToLower() == "true")
                //    {
                //        DataTable dt = getDataTable(sSql);
                //        if (dt != null)
                //        {
                //            ReportViewer1.LocalReport.DataSources.Clear();
                //            ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", dt));
                //        }
                //    }
                //    ReportViewer1.LocalReport.LoadReportDefinition(msReport);
                //    ReportViewer1.LocalReport.Refresh();
                //    msReport.Dispose();
                //}
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("GenerateReport : Error : " + Ex.Message);
            lbFF.Visible = false;
            ddlFileFormat.Visible = false;
            lnkExport.Visible = false;
            //lnkPrint.Enabled = false;
            Response.Redirect("Error.aspx?msg=Unable to generate the report");
        }
    }

    private bool getExportValue()
    {
        bool ret = false;
        HDDatabase hd = new HDDatabase();
        try
        {
            DataTable dt = hd.GetDataTable("select * from sy_acr_mem where af_row_id='" + "76cf74c043053300" + "'");
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    ret = dt.Rows[0]["mem_value1"].ToString().ToLower() == "true";
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getExportValue: " + e.Message);
        }
        finally
        {
            hd = null;
        }
        return ret;
    }

    private string homelink()
    {
        string retrn = "";
        try
        {

            Navigator ng = new Navigator();
            retrn = ng.getUserHomePage();
            ng = null;


        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error homlink :" + ex.Message);
        }
        return retrn;
    }

    private void setBackButtonLink()
    {
        if (!Page.IsPostBack)
        {
            imgback.Attributes.Add("onclick", "javascript:window.history.back(-1); return false;");

        }
        else
        {
            string previousscreen = "";
            if (HttpContext.Current.Request.Url.AbsoluteUri.ToString().Trim() != "")
            {
                Navigator ng = new Navigator();

                previousscreen = Navigator.getURL(HttpContext.Current.Request.Url.AbsoluteUri.ToString());
                //HttpContext.Current.Request.Url.AbsoluteUri.ToString();
            }

            //string previousscreen = HttpContext.Current.Request.UrlReferrer.ToString();

            //imgback.Attributes.Add("onclick", "javascript:window.history.back(-2); return false;");
            //imgback.Attributes.Add("onClick", "javascript:window.history.back(-2); return false;");
            imgback.Attributes.Add("onClick", "javascript:window.location.href=('" + previousscreen + "')");
        }
    }

}
